SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[DISC_CDS_RedemptionCodesReport]
	@Code varchar(8)
AS
BEGIN
	WITH CTE (RedemptionCode, DateTime) AS  
	(  
		SELECT 
			rc.RedemptionCode,
			f.datetime
		FROM DnnProduct_CDS_RedemptionCodes rc WITH (NOLOCK) 
		JOIN DISC_DistOrderItems dot WITH (NOLOCK) ON (rc.DistOrderItemId = dot.Id)
		JOIN DISC_Fulfillment f WITH (NOLOCK) ON (f.ordersitemID = dot.Id)
		WHERE rc.RedemptionCode = @Code AND rc.DownloadUrl LIKE '%download.aspx?PID=DistID%'

		UNION ALL  

		SELECT
			RedemptionCode,
			af.transtime
		FROM DnnProduct_CDS_RedemptionCodes rc WITH (NOLOCK) 
		JOIN DISC_DistOrderItems dot WITH (NOLOCK) ON (rc.DistOrderItemId = dot.Id)
		JOIN DISC_DistributorConfig dc  WITH (NOLOCK) ON dc.DistID = dot.DistID
		JOIN adept_dev..distributor ad WITH (NOLOCK) ON ad.DistUrl = dc.DistUrl
		JOIN adept_dev..fulfillment af WITH (NOLOCK) ON af.DistID = ad.DistID AND af.Confirmed = 'T' AND af.TransID = dbo.[GetACSTransactionId](rc.DownloadUrl)
		WHERE rc.RedemptionCode = @Code AND rc.DownloadUrl LIKE '%URLLink.acsm?action=enterorder%'
			AND EXISTS(
				SELECT * FROM adept_dev..fulfillmentitem afi WITH (NOLOCK) 
				JOIN adept_dev..license al WITH (NOLOCK) ON al.UserID = af.UserID AND al.ResourceID = afi.ResourceID
				WHERE afi.FulfillmentID = af.FulfillmentID
			)
	)
	SELECT RedemptionCode, DateTime FROM CTE   
	ORDER BY [DateTime] DESC
END
GO
